using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Reflection;

using Pampa.Grid.Core;
using Pampa.Grid.Core.Filters;
using Pampa.Grid.Exceptions;

using System.Data.SqlClient;

namespace Pampa.Grid.Data
{
    public class GridDataReaderSqlView : GridDataReader
    {
        SqlConnection conn;
        SqlDataAdapter dataAdapter;
        DataTable dataTable;

        string connStringName;
        string viewName;
        int cursor;

        public GridDataReaderSqlView() : base()
        {
            this.dataAdapter = new SqlDataAdapter();
        }

        public GridDataReaderSqlView(string connStringName, string viewName) : this()
        {
            this.ConnectionStringName = connStringName;
            this.ViewName = viewName;
        }
        
        #region Properties

        public string ConnectionStringName
        {
            get { return connStringName; }
            set { connStringName = value; }
        }

        public string ViewName
        {
            get { return viewName; }
            set { viewName = value; }
        }

        #endregion

        #region Public Functions

        public override void Open(Configuration configuration, int pageSize, int pageNumber)
        {
            OpenConnection();

            int from = pageSize * (pageNumber - 1);
            int length = pageSize;

            this.cursor = -1;

            try
            {
                // Si existe un orden definido deberia usar la tecnica del doble query para paginar en el servidor
                dataAdapter.SelectCommand = (SqlCommand)GetCommand(configuration);
                dataTable = new DataTable();
                dataAdapter.Fill(from, length, dataTable);
            }
            catch (DbException ex)
            {
                throw (new DataReaderException("Error in view '" + this.viewName + "': " + ex.Message));
            }
        }

        public override int GetCount(Configuration configuration)
        {
            OpenConnection();

            IDbCommand command = GetCountCommand(configuration);

            try
            {
                return Int32.Parse(command.ExecuteScalar().ToString());
            }
            catch (DbException ex)
            {
                throw (new DataReaderException("Error counting rows in view '" + this.viewName + "': " + ex.Message));
            }
            finally
            {
                Close();
            }
        }

        public override bool Read()
        {
            this.cursor++;
            return (this.cursor < dataTable.Rows.Count);
        }

        public override object GetValue(string fieldName)
        {
            if (dataTable.Columns.Contains(fieldName))
            {
                return dataTable.Rows[this.cursor][fieldName];
            }
            else
            {
                throw (new DataReaderException("There is no field defined with name '" + fieldName + "'"));
            }
        }

        public override void Close()
        {
            this.conn.Close();
            this.cursor = -1;
            this.dataTable = null;
        }

        private void OpenConnection()
        {
            if (this.conn == null)
            {
                System.Configuration.ConnectionStringSettings connString = System.Configuration.ConfigurationManager.ConnectionStrings[this.connStringName];

                if (connString == null)
                {
                    throw (new DataReaderException("There is no connection string called '" + this.connStringName + "'"));
                }
                else
                {
                    this.conn = new SqlConnection(connString.ConnectionString);
                }
            }

            this.conn.Open();
        }

        #endregion

        #region Xml Functions

        public override void LoadXml(System.Xml.XmlNode node)
        {
            XmlNamespaceManager nsManager = new XmlNamespaceManager(node.OwnerDocument.NameTable);
            nsManager.AddNamespace("pampa", node.NamespaceURI);

            XmlNode nodeDataSource = node.SelectSingleNode("pampa:DataSource", nsManager);
            if (nodeDataSource == null)
                throw (new GridConfigurationException("Node 'DataSource' was not found into the node 'DataReader'."));

            if (nodeDataSource.Attributes["Name"] == null)
                throw (new GridConfigurationException("Attribute 'ViewName' can not be found in node 'DataSource'."));

            this.ViewName = nodeDataSource.Attributes["Name"].Value.ToString();

            XmlNode nodeConnection = node.SelectSingleNode("pampa:ConnectionString", nsManager);
            if (nodeConnection == null)
                throw (new GridConfigurationException("Node 'ConnectionString' was not found into the node 'DataReader'."));

            if (nodeConnection.Attributes["Name"] == null)
                throw (new GridConfigurationException("Attribute 'Name' can not be found in node 'ConnectionString'."));

            this.ConnectionStringName = nodeConnection.Attributes["Name"].Value.ToString();
        }

        public override void ToXml(ref XmlNode root)
        {
            XmlDocument document = root.OwnerDocument;
            XmlNode nodeDataReader = document.CreateElement("DataReader");
            root.AppendChild(nodeDataReader);

            XmlAttribute attribute;
            attribute = document.CreateAttribute("Type");
            attribute.Value = this.GetType().FullName;
            nodeDataReader.Attributes.Append(attribute);

            XmlNode nodeDataSource = document.CreateElement("DataSource");
            attribute = document.CreateAttribute("ViewName");
            attribute.Value = this.ViewName;
            nodeDataSource.Attributes.Append(attribute);

            XmlNode nodeConnection = document.CreateElement("ConnectionString");
            attribute = document.CreateAttribute("Name");
            attribute.Value = this.ViewName;
            nodeConnection.Attributes.Append(attribute);

            nodeDataReader.AppendChild(nodeDataSource);
            nodeDataReader.AppendChild(nodeConnection);
        }

        #endregion

        #region Private Functions

        private IDbCommand GetCommand(Configuration configuration)
        {
            SqlCommandBuilder queryBuilder = GetQueryBuilder(configuration);
            IDbCommand command = queryBuilder.GetSelectCommand();
            command.Connection = this.conn;
            return command;
        }

        private IDbCommand GetCountCommand(Configuration configuration)
        {
            SqlCommandBuilder queryBuilder = GetQueryBuilder(configuration);
            IDbCommand command = queryBuilder.GetCountCommand();
            command.Connection = this.conn;
            return command;
        }

        private IDbCommand GetSmartCommand(Configuration configuration, int from, int length)
        {
            // Obtengo el count de la tabla
            IDbCommand countCommand = GetCountCommand(configuration);
            int count = Int32.Parse(countCommand.ExecuteScalar().ToString());
            
            // Obtengo el query de adentro
            SqlCommandBuilder queryBuilderInner = GetInnerQueryBuilder(configuration, from, length);
            IDbCommand innerCommand = queryBuilderInner.GetSelectCommand(count - from);

            // Obtengo el query de afuera
            SqlCommandBuilder queryBuilderOuter = new SqlCommandBuilder();
            queryBuilderOuter.Source = "(" + innerCommand.CommandText + ") as UpResult";

            foreach (KeyValuePair<string, OrderDirection> orderBy in configuration.OrderBy)
            {
                queryBuilderOuter.AddOrder(orderBy.Key, orderBy.Value);
            }

            IDbCommand outerCommand = innerCommand;
            outerCommand.CommandText = queryBuilderOuter.GetSelectCommand(length).CommandText;
            outerCommand.Connection = this.conn;

            return outerCommand;
        }

        private SqlCommandBuilder GetQueryBuilder(Configuration configuration)
        {
            SqlCommandBuilder queryBuilder = new SqlCommandBuilder();

            foreach (KeyValuePair<string, OrderDirection> orderBy in configuration.OrderBy)
            {
                queryBuilder.AddOrder(orderBy.Key, orderBy.Value);
            }

            // Vista de Origen - From ...
            queryBuilder.Source = this.viewName;

            // Criterios para filtrar - Where ...
            if ((configuration.MainFilter != null) && (configuration.MainFilter.InnerFilters.Count > 0))
            {
                queryBuilder.AddCriteria(configuration.MainFilter);
            }
            if ((configuration.StaticFilter != null) && (configuration.StaticFilter.InnerFilters.Count > 0))
            {
                queryBuilder.AddCriteria(configuration.StaticFilter);
            }
            if ((configuration.QuickFilter != null) && (configuration.QuickFilter.InnerFilters.Count > 0))
            {
                queryBuilder.AddCriteria(configuration.QuickFilter);
            }

            return queryBuilder;
        }

        private SqlCommandBuilder GetInnerQueryBuilder(Configuration configuration, int from, int length)
        {
            SqlCommandBuilder queryBuilderInner = new SqlCommandBuilder();

            // Vista de Origen - From ...
            queryBuilderInner.Source = this.viewName;

            // Criterios para filtrar - Where ...
            if ((configuration.MainFilter != null) && (configuration.MainFilter.InnerFilters.Count > 0))
            {
                queryBuilderInner.AddCriteria(configuration.MainFilter);
            }
            if ((configuration.StaticFilter != null) && (configuration.StaticFilter.InnerFilters.Count > 0))
            {
                queryBuilderInner.AddCriteria(configuration.StaticFilter);
            }
            if ((configuration.QuickFilter != null) && (configuration.QuickFilter.InnerFilters.Count > 0))
            {
                queryBuilderInner.AddCriteria(configuration.QuickFilter);
            }

            // Order By 
            foreach (KeyValuePair<string, OrderDirection> orderBy in configuration.OrderBy)
            {
                if (orderBy.Value == OrderDirection.Ascending)
                    queryBuilderInner.AddOrder(orderBy.Key, OrderDirection.Descending);
                else
                    queryBuilderInner.AddOrder(orderBy.Key, OrderDirection.Ascending);
            }

            return queryBuilderInner;
        }

        #endregion

    }
}
